package edu.pku.hawk.datasource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import edu.pku.hawk.controller.util.Config;
import edu.pku.hawk.controller.util.DetectorInfo;

public class GetValue {
	public static SensorData[] getValueBySensor(ConnectDB con, SensorType sensor, int sensorId) throws SQLException,
			ClassNotFoundException {
		ArrayList<SensorData> al = new ArrayList<SensorData>();

		SensorData tm = null;
		String s = "SELECT * FROM " + sensor + " where sensor_id= " + sensorId;
		ResultSet result = con.runSqlQuery(s);

		while (result.next()) {
			float value = result.getFloat("value");
			java.sql.Timestamp time = result.getTimestamp("time");
			int sid = result.getInt("sensor_id");
			tm = new SensorData(sid, value, time);
			al.add(tm);
		}
		result.close();
		SensorData[] tmp = new SensorData[al.size()];
		for (int i = 0; i < al.size(); i++) {
			tmp[i] = al.get(i);
		}
		return tmp;
	}

	// 2
	public static SensorData getValueBySensorAndTime(ConnectDB con, SensorType sensor, int sensorId, Timestamp intime)
			throws SQLException, ClassNotFoundException {
		SensorData tm = null;
		ResultSet result = con.runSqlQuery("SELECT * FROM " + sensor + ";");
		while (result.next()) {
			float value = result.getFloat("value");
			java.sql.Timestamp time = result.getTimestamp("time");
			if (time.equals(intime)) {
				tm = new SensorData(sensorId, value, time);
			}
		}
		result.close();
		return tm;
	}

	// 3
	public static SensorData[] getValueBySensorAndTimeSegment(ConnectDB con, SensorType sensor, int sensorId,
			Timestamp start, Timestamp end) throws SQLException, ClassNotFoundException {
		ArrayList<SensorData> al = new ArrayList<SensorData>();
		String s = new String();
		SensorData tm;
		s = "SELECT * FROM " + sensor + " WHERE " + sensor + ".time > " + "'" + start + "'" + " and " + sensor
				+ ".time <" + "'" + end + "'" + " HAVING sensor_id =" + sensorId + ";";
		ResultSet result = con.runSqlQuery(s);

		while (result.next()) {
			Object value = null;
			if (sensor == SensorType.sensor_infrared)
				value = result.getInt("value");
			else if (sensor == SensorType.sensor_metal)
				value = result.getInt("value");
			else if (sensor == SensorType.sensor_pressure)
				value = result.getFloat("value");
			else if (sensor == SensorType.sensor_smoke)
				value = result.getFloat("value");
			else if (sensor == SensorType.sensor_sound)
				value = result.getInt("value");
			else if (sensor == SensorType.sensor_rfid)
				value = result.getString("value");

			java.sql.Timestamp time = result.getTimestamp("time");
			int sid = result.getInt("sensor_id");
			tm = new SensorData(sid, value, time);
			al.add(tm);

		}
		result.close();
		SensorData[] tmp = new SensorData[al.size()];
		for (int i = 0; i < al.size(); i++) {
			tmp[i] = al.get(i);
		}
		return tmp;

	}

	// 4 ***
	public static SensorData getLastValueBySensor(ConnectDB con, SensorType sensor, int sensorId) throws SQLException,
			ClassNotFoundException {

		String s = new String();
		// s="select * from "+sensor+" where time = (select max(time) from
		// "+sensor+" where sensor_id= " + sensorId + ") and sensor_id=" +
		// sensorId;
		s = "select * from " + sensor + " where sensor_id=" + sensorId;
		SensorData tm = null;
		ResultSet result = con.runSqlQuery(s);

		while (result.next()) {
			Object value = null;
			if (sensor == SensorType.sensor_infrared)
				value = result.getInt("value");
			else if (sensor == SensorType.sensor_metal)
				value = result.getInt("value");
			else if (sensor == SensorType.sensor_pressure)
				value = result.getFloat("value");
			else if (sensor == SensorType.sensor_smoke)
				value = result.getFloat("value");
			else if (sensor == SensorType.sensor_sound)
				value = result.getInt("value");
			else if (sensor == SensorType.sensor_rfid)
				value = result.getString("value");

			java.sql.Timestamp time = result.getTimestamp("time");
			int sid = result.getInt("sensor_id");

			tm = new SensorData(sid, value, time);

		}
		result.close();

		return tm;

	}

	// 5
	public static SensorData[] getLastNValueBySensor(ConnectDB con, SensorType sensor, int n, int sensorId)
			throws SQLException, ClassNotFoundException {

		int i = 0;
		ArrayList<SensorData> al = new ArrayList<SensorData>();
		String s = new String();
		s = "select * from " + sensor + " where sensor_id=" + sensorId + " order by time desc";
		ResultSet result = con.runSqlQuery(s);

		while (result.next()) {

			if (i < n) {
				float value = result.getFloat("value");
				java.sql.Timestamp time = result.getTimestamp("time");
				int sid = result.getInt("sensor_id");
				al.add(i, new SensorData(sid, value, time));
				i = i + 1;
			}
		}
		result.close();
		SensorData[] tmp = new SensorData[al.size()];
		System.out.println("al.size()=" + al.size() + "\n");
		for (int j = 0; j < al.size(); j++) {
			tmp[j] = al.get(j);
		}
		return tmp;

	}

	// 6
	public static SensorData getLastValue(ConnectDB con, SensorType sensor) throws SQLException, ClassNotFoundException {

		int i = 0;
		String s = new String();
		s = "select * from " + sensor + " order by time desc";
		SensorData tm = null;
		ResultSet result = con.runSqlQuery(s);

		while (result.next() && i < 1) {
			float value = result.getFloat("value");
			java.sql.Timestamp time = result.getTimestamp("time");
			int sid = result.getInt("sensor_id");
			tm = new SensorData(sid, value, time);
			++i;
		}
		result.close();
		return tm;
	}

	// 7
	public static SensorData[] getValueByTimeSegment(ConnectDB con, SensorType sensor, Timestamp start, Timestamp end)
			throws SQLException, ClassNotFoundException {
		ArrayList<SensorData> al = new ArrayList<SensorData>();
		String s = new String();
		s = "SELECT * FROM " + sensor + " WHERE " + sensor + ".time > " + "'" + start + "'" + " and " + sensor
				+ ".time <" + "'" + end + "'" + "order by time asc";
		SensorData tm;
		ResultSet result = con.runSqlQuery(s);

		while (result.next()) {

			float value = result.getFloat("value");
			java.sql.Timestamp time = result.getTimestamp("time");
			int sid = result.getInt("sensor_id");
			tm = new SensorData(sid, value, time);
			al.add(tm);

		}
		result.close();
		SensorData[] tmp = new SensorData[al.size()];
		for (int j = 0; j < al.size(); j++) {
			tmp[j] = al.get(j);
		}
		return tmp;

	}

	// 8
	public static SensorData[] getLastNValue(ConnectDB con, SensorType sensor, int n) throws SQLException,
			ClassNotFoundException {

		ArrayList<SensorData> al = new ArrayList<SensorData>();
		int i = 0;
		String s = new String();
		s = "select * from " + sensor + " order by time desc";
		SensorData tm;
		ResultSet result = con.runSqlQuery(s);

		while (result.next() && i < n) {

			float value = result.getFloat("value");
			java.sql.Timestamp time = result.getTimestamp("time");
			int sid = result.getInt("sensor_id");
			tm = new SensorData(sid, value, time);
			al.add(tm);
			++i;

		}
		result.close();
		SensorData[] tmp = new SensorData[al.size()];
		for (int j = 0; j < al.size(); j++) {
			tmp[j] = al.get(j);
		}
		return tmp;

	}

	// 9
	public static SensorData[] getLastValueBySensor(ConnectDB con, SensorType sensor, int[] sensorId)
			throws SQLException, ClassNotFoundException {
		ArrayList<SensorData> al = new ArrayList<SensorData>();
		int count = 0;
		String s = null;
		s = "select * from " + sensor + " where time= (select max(time) from " + sensor + " where sensor_id ="
				+ sensorId[count] + ")";
		while (count < sensorId.length - 1) {

			s = s + " union select * from " + sensor + " where time= (select max(time) from " + sensor
					+ " where sensor_id =" + sensorId[count + 1] + ")";
			count++;

		}

		SensorData tm;
		
		
		Statement stmt = con.createStatement();
		ResultSet result = stmt.executeQuery(s);
//		ResultSet result = con.runSqlQuery(s);


		while (result.next()) {

			float value = result.getFloat("value");
			java.sql.Timestamp time = result.getTimestamp("time");
			int sid = result.getInt("sensor_id");
			tm = new SensorData(sid, value, time);
			al.add(tm);

		}
		result.close();
		SensorData[] tmp = new SensorData[al.size()];
		for (int j = 0; j < al.size(); j++) {
			tmp[j] = al.get(j);
		}
		return tmp;

	}

	// 10
	public static SensorData[] getLastNValueBySensor(ConnectDB con, SensorType sensor, int[] sensorId, int n)
			throws SQLException, ClassNotFoundException {
		ArrayList<SensorData> al = new ArrayList<SensorData>();
		int j = 0, count = 0;
		String s = null;
		SensorData tm;
		SensorData[] tmp;
		tmp = new SensorData[n * sensorId.length];
		while (count < sensorId.length) {

			s = "select * from " + sensor + " where sensor_id =" + sensorId[count] + " order by time desc";

			ResultSet result = con.runSqlQuery(s);

			for (j = 0; result.next() && j < n; ++j) {

				float value = result.getFloat("value");
				java.sql.Timestamp time = result.getTimestamp("time");
				int sid = result.getInt("sensor_id");
				tm = new SensorData(sid, value, time);
				al.add(tm);
				tmp[j + count * n] = al.get(j + count * n);
			}
			count++;

			result.close();

		}
		return tmp;
	}
	
//	public static RowSensorData[] get

	public static SensorData getLastValueBySensor_WSN(ConnectDB con, SensorType sensor, int sensorId)
			throws SQLException, ClassNotFoundException {

		if (sensor == SensorType.sensor_pressure || sensor == SensorType.sensor_infrared
				|| sensor == SensorType.sensor_metal) {

			Config config = Config.Singleton.Instance;
			DetectorInfo dinfo = config.getDetectorInfo(((Integer) (sensorId)).toString());

			String tableName = "sensors";
			int deviceAddr = dinfo.deviceAddr;
			if (deviceAddr != 1 && deviceAddr != 2 && deviceAddr != 3 && deviceAddr != 4)
				return null;
			int switchNum = dinfo.switchNum;
			if (switchNum <= 0 || switchNum > 8)
				return null;

			
			String s = new String();
			s = "select * from " + tableName + " where device_address=" + deviceAddr;
			Statement stmt = con.createStatement();
			ResultSet result = stmt.executeQuery(s);
			SensorData tm = null;
//			ResultSet result = con.runSqlQuery(s);
			

			while (result.next()) {
				Object value = null;
				if (sensor == SensorType.sensor_infrared || sensor == SensorType.sensor_metal) {
					int iovalue = result.getInt("io_value");
					int mask = 0x80;
					for (int i = 1; i < switchNum; i++) {
						mask >>= 1;
					}
					value = ((iovalue & mask) == 0) ? 0 : 1;

				} else if (sensor == SensorType.sensor_pressure) {
					switch (switchNum) {
					case 1:
						value = result.getInt("pressure_1");
						break;
					case 2:
						value = result.getInt("pressure_2");
						break;
					case 3:
						value = result.getInt("pressure_3");
						break;
					case 4:
						value = result.getInt("pressure_4");
						break;
					}
				}
				java.sql.Timestamp time = result.getTimestamp("time");

				tm = new SensorData(sensorId, value, time);
			}
			stmt.close();
			stmt = null;
			result.close();
			result = null;
			return tm;

			
		} else if (sensor == SensorType.sensor_rfid) {
			String s = new String();
			s = "select * from " + sensor + " where sensor_id=" + sensorId;
			SensorData tm = null;
			Statement stmt = con.createStatement();
			ResultSet result = stmt.executeQuery(s);

			while (result.next()) {
				Object value = result.getString("value");
				java.sql.Timestamp time = result.getTimestamp("time");
				int sid = result.getInt("sensor_id");
				tm = new SensorData(sid, value, time);
			}
			result.close();
			result = null;
			stmt.close();
			stmt = null;
			return tm;
			
			
		} else {
			return null;
		}
	}

	public static boolean dataInitialization(ConnectDB con) throws SQLException, ClassNotFoundException {

		int sensor_max = 25;
		int flag = 0;

		DataOperation insert = new DataOperation();

		if (insert.CreatTable(con, "sensor_pressure", false))
			System.out.println("create table successfully.");
		else
			flag++;
		if (insert.CreatTable(con, "sensor_sound", false))
			System.out.println("create table successfully.");
		else
			flag++;
		if (insert.CreatTable(con, "sensor_smoke", false))
			System.out.println("create table successfully.");
		else
			flag++;
		if (insert.CreatTable(con, "sensor_infrared", true))
			System.out.println("create table successfully.");
		else
			flag++;
		if (insert.CreatTable(con, "sensor_metal", true))
			System.out.println("create table successfully.");
		else
			flag++;

		if (insert.InsertFloatValue(con, sensor_max, "sensor_pressure"))
			System.out.println("Insert Pressure data successfully.");
		else
			flag++;

		if (insert.InsertFloatValue(con, sensor_max, "sensor_sound"))
			System.out.println("Insert Sound data successfully.");
		else
			flag++;
		if (insert.InsertFloatValue(con, sensor_max, "sensor_smoke"))
			System.out.println("Insert Smoke data successfully.");
		else
			flag++;
		if (insert.InsertBooleanValue(con, sensor_max, "sensor_infrared"))
			System.out.println("Insert Infrared data successfully.");
		else
			flag++;
		if (insert.InsertBooleanValue(con, sensor_max, "sensor_metal"))
			System.out.println("Insert Metal data successfully.");
		else
			flag++;

		if (flag == 0)
			return true;
		else
			return false;
	}

	public static void main(String[] args) throws SQLException, ClassNotFoundException, InterruptedException {

		int id = 12;
		int[] sid = { 1, 2, 3, 4, 5 };

		ConnectDB con = ConnectDB.getDBConnection("root", "root" ,"192.168.0.149:3306" , "demo");

		if (dataInitialization(con))
			;
		System.out.println("data operation successfully.");

		Thread.sleep(5000);

		String s = "2010-03-17 18:21:46";
		String r = "2010-03-22 11:19:38";
		Timestamp ti = java.sql.Timestamp.valueOf(s);
		Timestamp tj = java.sql.Timestamp.valueOf(r);

		System.out.println("1:getValueBySensor(int sensorId)\n");
		if (getValueBySensor(con, SensorType.sensor_infrared, id)[0] != null)
			System.out.println(id + " " + getValueBySensor(con, SensorType.sensor_infrared, id)[0].gettime() + "\n");

		System.out.println("2:getValueBySensorAndTime(int sensorId, Timestamp intime)\n");
		if (getValueBySensorAndTime(con, SensorType.sensor_infrared, id, ti) != null)
			System.out.println(id + " " + s + " " + getValueBySensorAndTime(con, SensorType.sensor_infrared, id, ti)
					+ "\n");

		System.out.println("3:getValueBySensorAndTimeSegment (int sensorId, Timestamp start, Timestamp end)\n");
		if (getValueBySensorAndTimeSegment(con, SensorType.sensor_infrared, id, ti, tj)[0] != null)
			System.out.println(id + " " + s + " to " + r + "\n"
					+ getValueBySensorAndTimeSegment(con, SensorType.sensor_infrared, id, ti, tj)[0].getvalue() + "\n");

		System.out.println("4:getLastValueBySensor (int sensorId)\n");
		if (getLastValueBySensor(con, SensorType.sensor_infrared, id) != null)
			System.out.println(getLastValueBySensor(con, SensorType.sensor_infrared, id).getvalue() + "\n");

		System.out.println("5:getLastNValueBySensor (int n, int sensorId)\n");
		if (getLastNValueBySensor(con, SensorType.sensor_infrared, 2, id)[0] != null)
			System.out.println(getLastNValueBySensor(con, SensorType.sensor_infrared, 2, id)[0].getvalue() + "\n");

		System.out.println("6:getLastValue ()\n");
		if (getLastValue(con, SensorType.sensor_infrared) != null)
			System.out.println(getLastValue(con, SensorType.sensor_infrared).getvalue() + "\n");

		System.out.println("7:getValueByTimeSegment(Timestamp start, Timestamp end)\n");
		if (getValueByTimeSegment(con, SensorType.sensor_infrared, ti, tj)[0] != null)
			System.out.println(getValueByTimeSegment(con, SensorType.sensor_infrared, ti, tj)[0].getvalue() + "\n");

		System.out.println("8:getLastNValue (int n)\n");
		if (getLastNValue(con, SensorType.sensor_infrared, 2)[0] != null)
			System.out.println(getLastNValue(con, SensorType.sensor_infrared, 2)[0].getvalue() + "\n");

		System.out.println("9:getLastValueBySensorAndTimeSegment (int[] sensorId)\n");
		if (getLastValueBySensor(con, SensorType.sensor_infrared, sid)[0] != null)
			System.out.println(getLastValueBySensor(con, SensorType.sensor_infrared, sid)[0].getvalue() + "\n");

		System.out.println("10:getLastNValueBySensor (int[] sensorId, int n)\n");
		if (getLastNValueBySensor(con, SensorType.sensor_infrared, sid, 2)[0] != null)
			System.out.println(getLastNValueBySensor(con, SensorType.sensor_infrared, sid, 2)[0].getvalue() + "\n");

	}

}
